//-----------------------------------------------------------------------
// <copyright file="USERDAOBase.cs" company="Pyramid Consulting">
//     Copyright (c) Pyramid Consulting. All rights reserved.
// </copyright>
//-----------------------------------------------------------------------

using System;
using System.Data;
using System.Collections;
using MyFramework;
using MyFramework.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using MIMS.WRS.Entities;

namespace MIMS.WRS.Data
{    
    public abstract class USERDAOBase
    {
        #region Common methods
        public virtual USER CreateUSERFromReader(IDataReader reader)
        {
            USER item = new USER();
            try
            {
                if (!reader.IsDBNull(reader.GetOrdinal("US_ID_PK"))) item.US_ID_PK = (int)reader["US_ID_PK"];
                if (!reader.IsDBNull(reader.GetOrdinal("NAME"))) item.NAME = (string)reader["NAME"];
                if (!reader.IsDBNull(reader.GetOrdinal("LOGIN_NAME"))) item.LOGIN_NAME = (string)reader["LOGIN_NAME"];
                if (!reader.IsDBNull(reader.GetOrdinal("PASSWORD"))) item.PASSWORD = (string)reader["PASSWORD"];
				if (!reader.IsDBNull(reader.GetOrdinal("FullPassword"))) item.FullPassword = (string)reader["FullPassword"];
                if (!reader.IsDBNull(reader.GetOrdinal("EMAIL"))) item.EMAIL = (string)reader["EMAIL"];
                if (!reader.IsDBNull(reader.GetOrdinal("USER_TYPE"))) item.USER_TYPE = (byte)reader["USER_TYPE"];
                if (!reader.IsDBNull(reader.GetOrdinal("CUSTOMER_CODE"))) item.CUSTOMER_CODE = (string)reader["CUSTOMER_CODE"];
                if (!reader.IsDBNull(reader.GetOrdinal("BAR_CODE"))) item.BAR_CODE = (string)reader["BAR_CODE"];
                if (!reader.IsDBNull(reader.GetOrdinal("IS_ADMIN"))) item.IS_ADMIN = (bool)reader["IS_ADMIN"];
                if (!reader.IsDBNull(reader.GetOrdinal("IS_ENABLED"))) item.IS_ENABLED = (bool)reader["IS_ENABLED"];
                if (!reader.IsDBNull(reader.GetOrdinal("CREATED_DATE"))) item.CREATED_DATE = (DateTime)reader["CREATED_DATE"];
                if (!reader.IsDBNull(reader.GetOrdinal("CREATED_BY"))) item.CREATED_BY = (int)reader["CREATED_BY"];
                if (!reader.IsDBNull(reader.GetOrdinal("UPDATED_DATE"))) item.UPDATED_DATE = (DateTime)reader["UPDATED_DATE"];
                if (!reader.IsDBNull(reader.GetOrdinal("UPDATED_BY"))) item.UPDATED_BY = (int)reader["UPDATED_BY"];
                if (!reader.IsDBNull(reader.GetOrdinal("CHANGE_PWD_GUID"))) item.CHANGE_PWD_GUID = (Guid)reader["CHANGE_PWD_GUID"];
                if (!reader.IsDBNull(reader.GetOrdinal("SORT_REFERENCE1"))) item.SORT_REFERENCE1 = (string)reader["SORT_REFERENCE1"];
                if (!reader.IsDBNull(reader.GetOrdinal("SORT_REFERENCE2"))) item.SORT_REFERENCE2 = (string)reader["SORT_REFERENCE2"];
                if (!reader.IsDBNull(reader.GetOrdinal("DOWNLOADABLE_CSV"))) item.DOWNLOADABLE_CSV = (bool)reader["DOWNLOADABLE_CSV"];
                if (!reader.IsDBNull(reader.GetOrdinal("ActiveOfficeID"))) item.ActiveOfficeID = (int)reader["ActiveOfficeID"];
                if (!reader.IsDBNull(reader.GetOrdinal("CountryID"))) item.CountryID = (int)reader["CountryID"];
				if (!reader.IsDBNull(reader.GetOrdinal("ViewSalesOrders"))) item.ViewSalesOrders = (bool)reader["ViewSalesOrders"];
            }
            catch (Exception ex)
            {
                // log this exception
                Logger.Error(ex);
                // wrap it and rethrow
                throw new DataAccessException(SR.DataAccessCreateUSERFromReaderException, ex);
            }
            return item;
        }
        #endregion
        
        #region CreateUSER methods
            
        public virtual void CreateUSER(USER user)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spUSERInsert");
                
                dbCommandWrapper.AddInParameter("@NAME", DbType.AnsiString, user.NAME);
                dbCommandWrapper.AddInParameter("@LOGIN_NAME", DbType.AnsiString, user.LOGIN_NAME);
                dbCommandWrapper.AddInParameter("@PASSWORD", DbType.AnsiString, user.PASSWORD);
                dbCommandWrapper.AddInParameter("@EMAIL", DbType.AnsiString, user.EMAIL);
                dbCommandWrapper.AddInParameter("@USER_TYPE", DbType.Byte, user.USER_TYPE);
                dbCommandWrapper.AddInParameter("@CUSTOMER_CODE", DbType.AnsiString, user.CUSTOMER_CODE);
                dbCommandWrapper.AddInParameter("@BAR_CODE", DbType.AnsiString, user.BAR_CODE);
                dbCommandWrapper.AddInParameter("@IS_ADMIN", DbType.Boolean, user.IS_ADMIN);
                dbCommandWrapper.AddInParameter("@IS_ENABLED", DbType.Boolean, user.IS_ENABLED);
                dbCommandWrapper.AddInParameter("@CREATED_DATE", DbType.DateTime, user.CREATED_DATE);
                dbCommandWrapper.AddInParameter("@CREATED_BY", DbType.Int32, user.CREATED_BY);
                dbCommandWrapper.AddInParameter("@UPDATED_DATE", DbType.DateTime, user.UPDATED_DATE);
                dbCommandWrapper.AddInParameter("@UPDATED_BY", DbType.Int32, user.UPDATED_BY);
                dbCommandWrapper.AddInParameter("@CHANGE_PWD_GUID", DbType.Guid, user.CHANGE_PWD_GUID);
                dbCommandWrapper.AddInParameter("@SORT_REFERENCE1", DbType.AnsiString, user.SORT_REFERENCE1);
                dbCommandWrapper.AddInParameter("@SORT_REFERENCE2", DbType.AnsiString, user.SORT_REFERENCE2);
                dbCommandWrapper.AddInParameter("@DOWNLOADABLE_CSV", DbType.Boolean, user.DOWNLOADABLE_CSV);
                dbCommandWrapper.AddInParameter("@ActiveOfficeID", DbType.Int32, user.ActiveOfficeID);
                dbCommandWrapper.AddInParameter("@CountryID", DbType.Int32, user.CountryID);
				dbCommandWrapper.AddInParameter("@ViewSalesOrders", DbType.Boolean, user.ViewSalesOrders);
                dbCommandWrapper.AddOutParameter("@US_ID_PK", DbType.Int32, 0);
                
                DataAccessHelper.ExecuteNonQuery(database, dbCommandWrapper);
                user.US_ID_PK = (int)dbCommandWrapper.GetParameterValue("@US_ID_PK");
            }
            catch (Exception ex)
            {
                // log this exception
                Logger.Error(ex);
                // wrap it and rethrow
                throw new DataAccessException(SR.DataAccessCreateUSERException, ex);
            }
        }

        #endregion

        #region UpdateUSER methods
        
        public virtual void UpdateUSER(USER user)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spUSERUpdate");            
                
                dbCommandWrapper.AddInParameter("@US_ID_PK", DbType.Int32, user.US_ID_PK);
                dbCommandWrapper.AddInParameter("@NAME", DbType.AnsiString, user.NAME);
                dbCommandWrapper.AddInParameter("@LOGIN_NAME", DbType.AnsiString, user.LOGIN_NAME);
                dbCommandWrapper.AddInParameter("@PASSWORD", DbType.AnsiString, user.PASSWORD);
                dbCommandWrapper.AddInParameter("@EMAIL", DbType.AnsiString, user.EMAIL);
                dbCommandWrapper.AddInParameter("@USER_TYPE", DbType.Byte, user.USER_TYPE);
                dbCommandWrapper.AddInParameter("@CUSTOMER_CODE", DbType.AnsiString, user.CUSTOMER_CODE);
                dbCommandWrapper.AddInParameter("@BAR_CODE", DbType.AnsiString, user.BAR_CODE);
                dbCommandWrapper.AddInParameter("@IS_ADMIN", DbType.Boolean, user.IS_ADMIN);
                dbCommandWrapper.AddInParameter("@IS_ENABLED", DbType.Boolean, user.IS_ENABLED);
                dbCommandWrapper.AddInParameter("@CREATED_DATE", DbType.DateTime, user.CREATED_DATE);
                dbCommandWrapper.AddInParameter("@CREATED_BY", DbType.Int32, user.CREATED_BY);
                dbCommandWrapper.AddInParameter("@UPDATED_DATE", DbType.DateTime, user.UPDATED_DATE);
                dbCommandWrapper.AddInParameter("@UPDATED_BY", DbType.Int32, user.UPDATED_BY);
                dbCommandWrapper.AddInParameter("@CHANGE_PWD_GUID", DbType.Guid, user.CHANGE_PWD_GUID);
                dbCommandWrapper.AddInParameter("@SORT_REFERENCE1", DbType.AnsiString, user.SORT_REFERENCE1);
                dbCommandWrapper.AddInParameter("@SORT_REFERENCE2", DbType.AnsiString, user.SORT_REFERENCE2);
                dbCommandWrapper.AddInParameter("@DOWNLOADABLE_CSV", DbType.Boolean, user.DOWNLOADABLE_CSV);
                dbCommandWrapper.AddInParameter("@ActiveOfficeID", DbType.Int32, user.ActiveOfficeID);
                dbCommandWrapper.AddInParameter("@CountryID", DbType.Int32, user.CountryID);
				dbCommandWrapper.AddInParameter("@ViewSalesOrders", DbType.Boolean, user.ViewSalesOrders);
                
                DataAccessHelper.ExecuteNonQuery(database, dbCommandWrapper);
            }
            catch (Exception ex)
            {
                // log this exception
                Logger.Error(ex);
                // wrap it and rethrow
                throw new DataAccessException(SR.DataAccessUpdateUSERException, ex);
            }
        }
        
        #endregion

        #region DeleteUSER methods
        public virtual void DeleteUSER(int us_id_pk)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spUSERDelete");
                
                dbCommandWrapper.AddInParameter("@US_ID_PK", DbType.Int32, us_id_pk);
                
                DataAccessHelper.ExecuteNonQuery(database, dbCommandWrapper);
            }
            catch (Exception ex)
            {
                // log this exception
                Logger.Error(ex);
                // wrap it and rethrow
                throw new DataAccessException(SR.DataAccessDeleteUSERException, ex);
            }
        }

        #endregion

        #region GetUSER methods
        
        public virtual USER GetUSER(int us_id_pk)
        {            
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spUSERGet");
                
                dbCommandWrapper.AddInParameter("@US_ID_PK", DbType.Int32, us_id_pk);
                
                USER user =  null;
                using (IDataReader reader = DataAccessHelper.ExecuteReader(database, dbCommandWrapper))
                {
                    if (reader.Read())
                    {
                        user = CreateUSERFromReader(reader);
                        reader.Close();
                    }
                }
                return user;
            }
            catch (Exception ex)
            {
                // log this exception
                Logger.Error(ex);
                // wrap it and rethrow
                throw new DataAccessException(SR.DataAccessGetUSERException, ex);
            }
        }
        
        #endregion

        #region GetUSERs methods
        public virtual USERCollection GetUSERs(USERColumns orderBy, OrderDirection orderDirection, int page, int pageSize, out int totalRecords)
        {            
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spUSERGetList");
                
                dbCommandWrapper.AddInParameter("@OrderBy", DbType.AnsiString, orderBy.ToString());
                dbCommandWrapper.AddInParameter("@OrderDirection", DbType.AnsiString, orderDirection.ToString());
                dbCommandWrapper.AddInParameter("@Page", DbType.Int32, page);
                dbCommandWrapper.AddInParameter("@PageSize", DbType.Int32, pageSize);
                dbCommandWrapper.AddOutParameter("@TotalRecords", DbType.Int32, 4);
                
                USERCollection userCollection = new USERCollection();
                using (IDataReader reader = DataAccessHelper.ExecuteReader(database, dbCommandWrapper))
                {
                    while (reader.Read())
                    {
                        USER user = CreateUSERFromReader(reader);
                        userCollection.Add(user);
                    }
                    reader.Close();
                }
                totalRecords = (int)dbCommandWrapper.GetParameterValue("@TotalRecords");
                return userCollection;
            }
            catch (Exception ex)
            {
                // log this exception
                Logger.Error(ex);
                // wrap it and rethrow
                throw new DataAccessException(SR.DataAccessGetUSERsException, ex);
            }
        }
        
        public virtual USERCollection GetUSERs(USERColumns orderBy, OrderDirection orderDirection)
        {            
            int totalRecords = 0;
            return GetUSERs(orderBy, orderDirection, 0, 0, out totalRecords);
        }
        
        #endregion
    }
}